package com.ruoyi.hbs.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.hbs.domain.*;
import com.ruoyi.hbs.mapper.HbsBusinessMappingMapper;
import com.ruoyi.hbs.mapper.HbsProductMappingMapper;
import com.ruoyi.hbs.mapper.HbsTerminalMappingMapper;
import com.ruoyi.hbs.service.*;
import com.ruoyi.system.mapper.SysDictDataMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;

import java.time.LocalDate;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.hbs.mapper.SingleSalesInventoryRecordsMapper;
import org.springframework.transaction.annotation.Transactional;

/**
 * 简化药品销售与库存记录表Impl
 */
@Service
@Slf4j
public class SingleSalesInventoryRecordsServiceImpl extends ServiceImpl<SingleSalesInventoryRecordsMapper, SingleSalesInventoryRecords> implements SingleSalesInventoryRecordsService {
    @Autowired
    private SingleSalesInventoryRecordsMapper singleSalesInventoryRecordsMapper;
    @Autowired
    private SalesInventoryRecordsService salesInventoryRecordsService;
    @Autowired
    private HbsProductMappingService hbsProductMappingService;
    @Autowired
    private HbsBusinessMappingService hbsBusinessMappingService;
    @Autowired
    private HbsTerminalMappingService hbsTerminalMappingService;
    @Autowired
    private ProductArchiveService productArchiveService;
    @Autowired
    private HbsBusinessArchiveService hbsBusinessArchiveService;
    @Autowired
    private HbsTerminalArchiveService hbsTerminalArchiveService;
    @Autowired
    private MsfxTerminalSalesFirstOrderService msfxTerminalSalesFirstOrderService;

    @Autowired
    private HbsProductMappingMapper hbsProductMappingMapper;
    @Autowired
    private HbsTerminalMappingMapper hbsTerminalMappingMapper;
    @Autowired
    private HbsBusinessMappingMapper hbsBusinessMappingMapper;


    /**
     * 同步导入码上放心数据
     */
    public void syncImport() {
        // 1.获取前一天数据
        List<SalesInventoryRecords> salesInventoryRecordsList = salesInventoryRecordsService.list(new LambdaQueryWrapper<SalesInventoryRecords>()
                .ge(SalesInventoryRecords::getCreatedTime, LocalDate.now().minusDays(1).atStartOfDay()));

        if (salesInventoryRecordsList.isEmpty()) {
            return;
        }

        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        try {
            // 提取所有唯一标识
            List<String> billIdDetailIds = salesInventoryRecordsList.stream()
                    .map(SalesInventoryRecords::getBillIdDetailId)
                    .filter(org.springframework.util.StringUtils::hasText)
                    .distinct()
                    .collect(Collectors.toList());

            // 批量查询已存在的记录
            Set<String> existingBillIdDetailIds;
            if (!billIdDetailIds.isEmpty()) {
                List<SingleSalesInventoryRecords> existingRecords = singleSalesInventoryRecordsMapper.selectList(
                        new LambdaQueryWrapper<SingleSalesInventoryRecords>()
                                .ge(SingleSalesInventoryRecords::getCreatedTime, LocalDate.now().minusDays(1).atStartOfDay())
                                .in(SingleSalesInventoryRecords::getBillIdDetailId, billIdDetailIds)
                                .select(SingleSalesInventoryRecords::getBillIdDetailId)
                );

                existingBillIdDetailIds = existingRecords.stream()
                        .map(SingleSalesInventoryRecords::getBillIdDetailId)
                        .collect(Collectors.toSet());
            } else {
                existingBillIdDetailIds = new HashSet<>();
            }

            // 筛选出需要插入的数据
            List<SalesInventoryRecords> toSalesInventoryRecordsInsertList = salesInventoryRecordsList.stream()
                    .filter(record -> record.getBillIdDetailId() != null &&
                            !existingBillIdDetailIds.contains(record.getBillIdDetailId()))
                    .collect(Collectors.toList());

            List<SingleSalesInventoryRecords> toInsertList = toSalesInventoryRecordsInsertList.stream()
                    .map(record -> {
                        SingleSalesInventoryRecords singleSalesInventoryRecords = new SingleSalesInventoryRecords();
                        BeanUtils.copyProperties(record, singleSalesInventoryRecords);
                        return singleSalesInventoryRecords;
                    })
                    .collect(Collectors.toList());
            // 批量插入
            if (!toInsertList.isEmpty()) {
                boolean result = this.saveBatch(toInsertList, 1000);
                if (result) {
                    successNum = toInsertList.size();
                    successMsg.append("成功导入 ").append(successNum).append(" 条记录");
                } else {
                    failureNum = toInsertList.size();
                    failureMsg.append("导入失败 ").append(failureNum).append(" 条记录");
                }
            } else {
                successMsg.append("所有数据已存在，无需导入");
            }

        } catch (Exception e) {
            log.error("导入销售库存记录时发生异常", e);
            failureNum = salesInventoryRecordsList.size();
            failureMsg.append("导入过程中发生异常：").append(e.getMessage());
        }

        // 处理结果
        if (failureNum > 0) {
            failureMsg.insert(0, "导入完成，其中成功 " + successNum + " 条，失败 " + failureNum + " 条。");
            throw new ServiceException(failureMsg.toString());
        } else {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，详情如下：");
            log.info(successMsg.toString());

        }
    }


    /**
     * 处理终端首单记录
     */
    @Override
    public void msfxProcessTerminalFirstOrder() {
        // 查询指定范围内的未处理记录
        LambdaQueryWrapper<SingleSalesInventoryRecords> queryWrapper = new LambdaQueryWrapper<>();
//        queryWrapper.ge(SingleSalesInventoryRecords::getId, 700000);
//        queryWrapper.le(SingleSalesInventoryRecords::getId, 900000);
        queryWrapper.eq(SingleSalesInventoryRecords::getIsProcess, 1);
        List<SingleSalesInventoryRecords> unmatchedRecords = singleSalesInventoryRecordsMapper.selectList(queryWrapper);
        if (unmatchedRecords.isEmpty()) {
            return;
        }
        for (SingleSalesInventoryRecords record : unmatchedRecords) {
            // 处理发货单位（如果是终端类型）
            processTerminalFirstOrder(record, true);
            // 处理收货单位（如果是终端类型）
            processTerminalFirstOrder(record, false);
            // 更新处理状态(首单已处理
            record.setIsProcess(2);
            singleSalesInventoryRecordsMapper.updateById(record);
        }
    }

    /**
     * 处理单个记录的终端首单逻辑
     *
     * @param record   销售记录
     * @param isSender 是否为发货方处理
     */
    private void processTerminalFirstOrder(SingleSalesInventoryRecords record, boolean isSender) {
        // 判断单位类型是否为终端类型
        String unitType = isSender ? record.getSenderUnitType() : record.getReceiverUnitType();

        // 终端类型："1"-零售, "6"-医疗单位(发货); "1"-零售, "6"-医疗单位(收货)
        boolean isTerminal = isSender ?
                ("1".equals(unitType) || "6".equals(unitType)) :
                ("1".equals(unitType) || "6".equals(unitType));

        if (!isTerminal) {
            return;
        }
        // 获取单位名称和信用代码
        String unitName = isSender ? record.getSenderUnit() : record.getReceiverUnit();
        String creditCode = isSender ? record.getSenderCreditCode() : record.getReceiverCreditCode();
        // 1. 根据单位名称和信用代码查询终端映射表获取终端ID
        LambdaQueryWrapper<HbsTerminalMapping> terminalMappingQuery = new LambdaQueryWrapper<>();
        terminalMappingQuery.eq(HbsTerminalMapping::getMsfxEnterpriseName, unitName);
        terminalMappingQuery.eq(HbsTerminalMapping::getMsfxCreditCode, creditCode);
        HbsTerminalMapping hbsTerminalMapping = hbsTerminalMappingService.getOne(terminalMappingQuery);
        if (ObjectUtil.isNull(hbsTerminalMapping)) {
            log.info("未找到终端映射，跳过处理，unitType{},终端名称{},终端code ",unitType, unitName, creditCode);
            return; // 未找到终端映射，跳过处理
        }

        // 2. 根据产品名称、规格、包装规格查询产品映射表获取产品ID
        LambdaQueryWrapper<HbsProductMapping> productMappingQuery = new LambdaQueryWrapper<>();
        productMappingQuery.eq(HbsProductMapping::getMsfxGenericName, record.getDrugGenericName());
        productMappingQuery.eq(HbsProductMapping::getMsfxSpecification, record.getSpecification());
        productMappingQuery.eq(HbsProductMapping::getMsfxPackageSpec, record.getPackageSpec());
//        productMappingQuery.isNotNull(HbsProductMapping::getHbsItemId);
        HbsProductMapping hbsProductMapping = hbsProductMappingService.getOne(productMappingQuery);
        if (ObjectUtil.isNull(hbsProductMapping)) {
            log.info("未找到产品映射，跳过处理，产品名称{},规格{},包装规格 ",record.getDrugGenericName(), record.getSpecification(), record.getPackageSpec());
            return; // 未找到产品映射，跳过处理
        }
//        // 遍历所有可能的终端和产品组合
        Long terminalId = hbsTerminalMapping.getId();
        Long productId = hbsProductMapping.getId();

        // 3. 查询是否已有该终端和产品的首单记录
        LambdaQueryWrapper<MsfxTerminalSalesFirstOrder> firstOrderQuery = new LambdaQueryWrapper<>();
        firstOrderQuery.eq(MsfxTerminalSalesFirstOrder::getTerminalMappingId, terminalId);
        firstOrderQuery.eq(MsfxTerminalSalesFirstOrder::getProductMappingId, productId);
        MsfxTerminalSalesFirstOrder existingFirstOrder = msfxTerminalSalesFirstOrderService.getOne(firstOrderQuery);
        if (existingFirstOrder != null) {
            // 4. 如果存在记录，比较日期并更新
            if (record.getOrderTime() != null &&
                    (existingFirstOrder.getFirstSaleDate() == null ||
                            record.getOrderTime().before(existingFirstOrder.getFirstSaleDate()))) {
                existingFirstOrder.setFirstSaleDate(record.getOrderTime());
                msfxTerminalSalesFirstOrderService.updateById(existingFirstOrder);
            }
        } else {
            // 5. 如果不存在记录，插入新记录
            MsfxTerminalSalesFirstOrder newFirstOrder = new MsfxTerminalSalesFirstOrder();
            newFirstOrder.setTerminalMappingId(terminalId);
            newFirstOrder.setProductMappingId(productId);
            newFirstOrder.setFirstSaleDate(record.getOrderTime());
            msfxTerminalSalesFirstOrderService.save(newFirstOrder);
        }
    }

    /**
     * 聚合补充处理未匹配的记录
     */
    @Override
    public void processUnmatchedRecordes() {
        // 查询未匹配的记录
        List<SingleSalesInventoryRecords> productList = singleSalesInventoryRecordsMapper.selecteProductList();
        List<SingleSalesInventoryRecords> businessList = singleSalesInventoryRecordsMapper.selecteBusinessList();
        List<SingleSalesInventoryRecords> terminalList = singleSalesInventoryRecordsMapper.selectTerminalList();
        List<HbsProductMapping> mappingsProductToInsert = new ArrayList<>();
        List<HbsProductMapping> empProductToInsert = new ArrayList<>();
        List<HbsBusinessMapping> mappingsBussinessToInsert = new ArrayList<>();
        List<HbsBusinessMapping> empBussinessToInsert = new ArrayList<>();
        List<HbsTerminalMapping> mappingsTerminalToInsert = new ArrayList<>();
        List<HbsTerminalMapping> empTerminalToInsert = new ArrayList<>();
        for (SingleSalesInventoryRecords record : productList) {
            // 提取 Excel 规格中的括号内容
            String excelSpec = extractSpecificationFromExcel(record.getSpecification());
            // 查询hbs产品档案中的匹配项
            LambdaQueryWrapper<ProductArchive> productQuery = new LambdaQueryWrapper<>();
            productQuery.eq(ProductArchive::getProductName, record.getDrugGenericName());
            productQuery.eq(ProductArchive::getSpecification, excelSpec); // 使用提取后的规格
            productQuery.eq(ProductArchive::getSalesStatus, "在售");
            List<ProductArchive> existingProducts = productArchiveService.list(productQuery);
            if (CollectionUtil.isNotEmpty(existingProducts)) {
                for (ProductArchive existingProduct : existingProducts) {
                    // 创建映射关系
                    HbsProductMapping mapping = HbsProductMapping.builder()
                            .msfxGenericName(record.getDrugGenericName())
                            .msfxSpecification(record.getSpecification())
                            .msfxPackageSpec(record.getPackageSpec())
                            .build();

                    // 根据单位类型调整字段
                    if ("1".equals(record.getSmallestUnitCode())) {
                        // 盒：使用包装规格
                        Integer calculatedPiecesPerBox = calculatePiecesPerBox(existingProduct.getPackagingSpec());
                        if (calculatedPiecesPerBox != null) {
                            Integer excelPiecesPerBox = parsePiecePerBox(record.getPackageSpec());
                            if (excelPiecesPerBox != null && excelPiecesPerBox.equals(calculatedPiecesPerBox)) {
                                mapping.setHbsItemId(existingProduct.getId());
                                mapping.setHbsProductName(existingProduct.getProductName());
                                mapping.setHbsSpecification(existingProduct.getSpecification());
                                mapping.setHbsPackageSpec(existingProduct.getPackagingSpec());
                            }
                        }
                    } else if ("2".equals(record.getSmallestUnitCode())) {
                        // 瓺：使用最小制剂数量
                        Integer piecesPerBottle = parsePiecePerBottle(existingProduct.getPackagingSpec());
                        if (piecesPerBottle != null) {
                            Integer excelPiecePerBottle = parsePiecePerBottle(record.getPackageSpec());
                            if (excelPiecePerBottle != null && excelPiecePerBottle.equals(piecesPerBottle)) {
                                mapping.setHbsItemId(existingProduct.getId());
                                mapping.setHbsProductName(existingProduct.getProductName());
                                mapping.setHbsSpecification(existingProduct.getSpecification());
                                mapping.setHbsPackageSpec(existingProduct.getPackagingSpec());
                            }
                        }
                    }
                    mappingsProductToInsert.add(mapping);
                }

            } else {
                HbsProductMapping newProduct = HbsProductMapping.builder()
                        .msfxGenericName(record.getDrugGenericName())
                        .msfxSpecification(record.getSpecification())
                        .msfxPackageSpec(record.getPackageSpec())
                        .build();
                empProductToInsert.add(newProduct);

            }
        }
        for (SingleSalesInventoryRecords record : businessList) {
            // 查询商业档案
            List<HbsBusinessArchive> existingHbsBusinessArchives = findBusinessArchives(
                    record.getSenderCreditCode(),
                    record.getSenderUnit()
            );
            if (CollectionUtil.isNotEmpty(existingHbsBusinessArchives)) {
                // 收集需要插入的商业映射
                for (HbsBusinessArchive existingBusiness : existingHbsBusinessArchives) {
                    HbsBusinessMapping newHbsBusinessMapping = HbsBusinessMapping.builder()
                            .hbsItemId(existingBusiness.getId())
                            .msfxCustomerName(record.getSenderUnit())
                            .msfxCreditCode(record.getSenderCreditCode())
                            .hbsCustomerName(existingBusiness.getCustomerName())
                            .hbsTaxNumber(existingBusiness.getTaxId())
                            .build();
                    mappingsBussinessToInsert.add(newHbsBusinessMapping);
                }
            } else {
                HbsBusinessMapping newHbsBusinessMapping = HbsBusinessMapping.builder()
                        .msfxCustomerName(record.getSenderUnit())
                        .msfxCreditCode(record.getSenderCreditCode())
                        .build();
                empBussinessToInsert.add(newHbsBusinessMapping);
            }
        }
        for (SingleSalesInventoryRecords record : terminalList) {
            // 查询终端档案
            List<HbsTerminalArchive> existingHbsTerminalArchives = findTerminalArchives(
                    record.getSenderCreditCode(),
                    record.getSenderUnit()
            );
            if (CollectionUtil.isNotEmpty(existingHbsTerminalArchives)) {
                for (HbsTerminalArchive existingTerminal : existingHbsTerminalArchives) {
                    HbsTerminalMapping newHbsTerminalMapping = HbsTerminalMapping.builder()
                            .hbsItemId(existingTerminal.getId())
                            .msfxEnterpriseName(record.getSenderUnit())
                            .msfxCreditCode(record.getSenderCreditCode())
                            .hbsTerminalName(existingTerminal.getTerminalName())
                            .hbsCreditCode(existingTerminal.getSocialCreditCode())
                            .build();
                    mappingsTerminalToInsert.add(newHbsTerminalMapping);
                }

            } else {
                HbsTerminalMapping newHbsTerminalMapping = HbsTerminalMapping.builder()
                        .msfxEnterpriseName(record.getSenderUnit())
                        .msfxCreditCode(record.getSenderCreditCode())
                        .build();
                empTerminalToInsert.add(newHbsTerminalMapping);
            }
        }

        if (CollectionUtil.isNotEmpty(mappingsProductToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsProductMapping> uniqueMappings = new HashMap<>();
            for (HbsProductMapping mapping : mappingsProductToInsert) {
                String key = generateMappingKey(
                        mapping.getHbsItemId(),
                        mapping.getMsfxGenericName(),
                        mapping.getMsfxSpecification(),
                        mapping.getMsfxPackageSpec(),
                        mapping.getHbsProductName(),
                        mapping.getHbsSpecification(),
                        mapping.getHbsPackageSpec()
                );
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsProductMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsProductMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsProductMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsProductMapping::getMsfxGenericName, record.getMsfxGenericName())
                            .eq(HbsProductMapping::getMsfxSpecification, record.getMsfxSpecification())
                            .eq(HbsProductMapping::getMsfxPackageSpec, record.getMsfxPackageSpec());
                    if (hbsProductMappingService.count(checkQuery) > 0) {
                        checkQuery.isNull(HbsProductMapping::getHbsItemId);
                        HbsProductMapping existing = hbsProductMappingService.getOne(checkQuery);
                        if (existing != null && existing.getHbsItemId() == null) {
                            // 更新现有记录
                            BeanUtils.copyProperties(record, existing, "id");
                            hbsProductMappingService.updateById(existing);
                        } else {
                            log.info("已存在完整映射，跳过插入：{}", record);
                            continue;
                        }
                    }
                    hbsProductMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建产品映射批量插入失败", e);
            }

        }
        if (CollectionUtil.isNotEmpty(empProductToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsProductMapping> empProductToInsertuniqueMappings = new HashMap<>();
            for (HbsProductMapping mapping : empProductToInsert) {
                String key = mapping.getMsfxGenericName() + "_" + mapping.getMsfxSpecification() + "_" + mapping.getMsfxPackageSpec();
                empProductToInsertuniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsProductMapping> empProductuniqueList = new ArrayList<>(empProductToInsertuniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsProductMapping record : empProductuniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsProductMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsProductMapping::getMsfxGenericName, record.getMsfxGenericName())
                            .eq(HbsProductMapping::getMsfxSpecification, record.getMsfxSpecification())
                            .eq(HbsProductMapping::getMsfxPackageSpec, record.getMsfxPackageSpec());

                    if (hbsProductMappingService.count(checkQuery) > 0) {
                        checkQuery.isNull(HbsProductMapping::getHbsItemId);
                        HbsProductMapping existing = hbsProductMappingService.getOne(checkQuery);
                        if (existing != null && existing.getHbsItemId() == null) {
                            // 更新现有记录
                            BeanUtils.copyProperties(record, existing, "id");
                            hbsProductMappingService.updateById(existing);
                        } else {
                            log.info("已存在完整映射，跳过插入：{}", record);
                            continue;
                        }
                    }
                    hbsProductMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建产品映射批量插入失败", e);
            }
        }
        int productResult = hbsProductMappingMapper.deleteDuplicateRecords();
        log.info("成功清理重复数据：产品映射 {} 条", productResult);

        int b = 1;

        //商业
        if (CollectionUtil.isNotEmpty(mappingsBussinessToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsBusinessMapping> uniqueMappings = new HashMap<>();
            for (HbsBusinessMapping mapping : mappingsBussinessToInsert) {
                String key = generateBusinessMappingKey(mapping.getHbsItemId(), mapping.getMsfxCustomerName(), mapping.getMsfxCreditCode(), mapping.getHbsCustomerName(), mapping.getHbsTaxNumber());
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsBusinessMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsBusinessMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsBusinessMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsBusinessMapping::getMsfxCustomerName, record.getMsfxCustomerName())
                            .eq(HbsBusinessMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCustomerName()), HbsBusinessMapping::getHbsCustomerName, record.getHbsCustomerName())
                            .eq(StringUtils.isNotEmpty(record.getHbsTaxNumber()), HbsBusinessMapping::getHbsTaxNumber, record.getHbsTaxNumber())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsBusinessMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsBusinessMappingService.count(checkQuery) > 0) {
                        log.info("已存在商业相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsBusinessMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建商业映射批量插入失败", e);
            }
        }
        if (CollectionUtil.isNotEmpty(empBussinessToInsert)) {
            // 使用 Set 去重，基于 msfxCustomerName 和 msfxCreditCode 组合
            Map<String, HbsBusinessMapping> uniqueMappings = new HashMap<>();
            for (HbsBusinessMapping mapping : empBussinessToInsert) {
                String key = mapping.getMsfxCustomerName() + "_" + mapping.getMsfxCreditCode();
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsBusinessMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsBusinessMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsBusinessMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsBusinessMapping::getMsfxCustomerName, record.getMsfxCustomerName())
                            .eq(HbsBusinessMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCustomerName()), HbsBusinessMapping::getHbsCustomerName, record.getHbsCustomerName())
                            .eq(StringUtils.isNotEmpty(record.getHbsTaxNumber()), HbsBusinessMapping::getHbsTaxNumber, record.getHbsTaxNumber())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsBusinessMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsBusinessMappingService.count(checkQuery) > 0) {
                        log.info("已存在商业相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsBusinessMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建商业映射批量插入失败", e);
            }
        }
        //终端映射
        if (CollectionUtil.isNotEmpty(mappingsTerminalToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsTerminalMapping> uniqueMappings = new HashMap<>();
            for (HbsTerminalMapping mapping : mappingsTerminalToInsert) {
                String key = generateTerminalMappingKey(mapping.getHbsItemId(), mapping.getMsfxEnterpriseName(), mapping.getMsfxCreditCode(), mapping.getHbsTerminalName(), mapping.getHbsCreditCode());
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsTerminalMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsTerminalMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsTerminalMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsTerminalMapping::getMsfxEnterpriseName, record.getMsfxEnterpriseName())
                            .eq(HbsTerminalMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCreditCode()), HbsTerminalMapping::getHbsCreditCode, record.getHbsCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsTerminalName()), HbsTerminalMapping::getHbsTerminalName, record.getHbsTerminalName())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsTerminalMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsTerminalMappingService.count(checkQuery) > 0) {
                        log.info("已存在终端相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsTerminalMappingService.save(record);
                }

            } catch (Exception e) {
                log.error("新建终端映射批量插入失败", e);
            }
        }
        if (CollectionUtil.isNotEmpty(empTerminalToInsert)) {
            // 使用 Set 去重，基于 msfxCustomerName 和 msfxCreditCode 组合
            Map<String, HbsTerminalMapping> uniqueMappings = new HashMap<>();
            for (HbsTerminalMapping mapping : empTerminalToInsert) {
                String key = mapping.getMsfxEnterpriseName() + "_" + mapping.getMsfxCreditCode();
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsTerminalMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsTerminalMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsTerminalMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsTerminalMapping::getMsfxEnterpriseName, record.getMsfxEnterpriseName())
                            .eq(HbsTerminalMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCreditCode()), HbsTerminalMapping::getHbsCreditCode, record.getHbsCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsTerminalName()), HbsTerminalMapping::getHbsTerminalName, record.getHbsTerminalName())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsTerminalMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsTerminalMappingService.count(checkQuery) > 0) {
                        log.info("已存在终端相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsTerminalMappingService.save(record);
                }

                log.info("成功插入 {} 条新建终端映射记录", uniqueList.size());
            } catch (Exception e) {
                log.error("新建终端映射批量插入失败", e);
            }
        }

    }


    /**
     * 处理未匹配的记录
     */
    @Override
    public void processUnmatchedRecords() {
        // 查询未匹配的记录
        LambdaQueryWrapper<SingleSalesInventoryRecords> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SingleSalesInventoryRecords::getIsProcess, 0);
        List<SingleSalesInventoryRecords> unmatchedRecords = singleSalesInventoryRecordsMapper.selectList(queryWrapper);
        List<HbsProductMapping> mappingsProductToInsert = new ArrayList<>();
        List<HbsProductMapping> empProductToInsert = new ArrayList<>();
        List<HbsBusinessMapping> mappingsBussinessToInsert = new ArrayList<>();
        List<HbsBusinessMapping> empBussinessToInsert = new ArrayList<>();
        List<HbsTerminalMapping> mappingsTerminalToInsert = new ArrayList<>();
        List<HbsTerminalMapping> empTerminalToInsert = new ArrayList<>();

        for (SingleSalesInventoryRecords record : unmatchedRecords) {
            // 产品匹配
            boolean matchAndSaveProduct = matchAndSaveProduct(mappingsProductToInsert, empProductToInsert, record);
            boolean matchAndSaveBusiness = matchAndSaveBusiness(mappingsBussinessToInsert, empBussinessToInsert, record);
            boolean matchAndSaveTerminal = matchAndSaveTerminal(mappingsTerminalToInsert, empTerminalToInsert, record);
            // 更新匹配状态
            record.setIsProcess(1);
            record.setIsMatched(matchAndSaveProduct && matchAndSaveBusiness && matchAndSaveTerminal ? 1 : 0);
            record.setRemarks("matchAndSaveProduct:" + matchAndSaveProduct + ",matchAndSaveBusiness:" + matchAndSaveBusiness + ",matchAndSaveTerminal:" + matchAndSaveTerminal);
            singleSalesInventoryRecordsMapper.updateById(record);
        }
        if (CollectionUtil.isNotEmpty(mappingsProductToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsProductMapping> uniqueMappings = new HashMap<>();
            for (HbsProductMapping mapping : mappingsProductToInsert) {
                String key = generateMappingKey(
                        mapping.getHbsItemId(),
                        mapping.getMsfxGenericName(),
                        mapping.getMsfxSpecification(),
                        mapping.getMsfxPackageSpec(),
                        mapping.getHbsProductName(),
                        mapping.getHbsSpecification(),
                        mapping.getHbsPackageSpec()
                );
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsProductMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsProductMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsProductMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsProductMapping::getMsfxGenericName, record.getMsfxGenericName())
                            .eq(HbsProductMapping::getMsfxSpecification, record.getMsfxSpecification())
                            .eq(HbsProductMapping::getMsfxPackageSpec, record.getMsfxPackageSpec());
                    if (hbsProductMappingService.count(checkQuery) > 0) {
                        checkQuery.isNull(HbsProductMapping::getHbsItemId);
                        HbsProductMapping existing = hbsProductMappingService.getOne(checkQuery);
                        if (existing != null && existing.getHbsItemId() == null) {
                            // 更新现有记录
                            BeanUtils.copyProperties(record, existing, "id");
                            hbsProductMappingService.updateById(existing);
                        } else {
                            log.info("已存在完整映射，跳过插入：{}", record);
                            continue;
                        }
                    }
                    hbsProductMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建产品映射批量插入失败", e);
            }

        }
        if (CollectionUtil.isNotEmpty(empProductToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsProductMapping> empProductToInsertuniqueMappings = new HashMap<>();
            for (HbsProductMapping mapping : empProductToInsert) {
                String key = mapping.getMsfxGenericName() + "_" + mapping.getMsfxSpecification() + "_" + mapping.getMsfxPackageSpec();
                empProductToInsertuniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsProductMapping> empProductuniqueList = new ArrayList<>(empProductToInsertuniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsProductMapping record : empProductuniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsProductMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsProductMapping::getMsfxGenericName, record.getMsfxGenericName())
                            .eq(HbsProductMapping::getMsfxSpecification, record.getMsfxSpecification())
                            .eq(HbsProductMapping::getMsfxPackageSpec, record.getMsfxPackageSpec());

                    if (hbsProductMappingService.count(checkQuery) > 0) {
                        checkQuery.isNull(HbsProductMapping::getHbsItemId);
                        HbsProductMapping existing = hbsProductMappingService.getOne(checkQuery);
                        if (existing != null && existing.getHbsItemId() == null) {
                            // 更新现有记录
                            BeanUtils.copyProperties(record, existing, "id");
                            hbsProductMappingService.updateById(existing);
                        } else {
                            log.info("已存在完整映射，跳过插入：{}", record);
                            continue;
                        }
                    }
                    hbsProductMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建产品映射批量插入失败", e);
            }
        }
        int productResult = hbsProductMappingMapper.deleteDuplicateRecords();
        log.info("成功清理重复数据：产品映射 {} 条", productResult);
        //商业
        if (CollectionUtil.isNotEmpty(mappingsBussinessToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsBusinessMapping> uniqueMappings = new HashMap<>();
            for (HbsBusinessMapping mapping : mappingsBussinessToInsert) {
                String key = generateBusinessMappingKey(mapping.getHbsItemId(), mapping.getMsfxCustomerName(), mapping.getMsfxCreditCode(), mapping.getHbsCustomerName(), mapping.getHbsTaxNumber());
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsBusinessMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsBusinessMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsBusinessMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsBusinessMapping::getMsfxCustomerName, record.getMsfxCustomerName())
                            .eq(HbsBusinessMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCustomerName()), HbsBusinessMapping::getHbsCustomerName, record.getHbsCustomerName())
                            .eq(StringUtils.isNotEmpty(record.getHbsTaxNumber()), HbsBusinessMapping::getHbsTaxNumber, record.getHbsTaxNumber())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsBusinessMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsBusinessMappingService.count(checkQuery) > 0) {
                        log.info("已存在商业相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsBusinessMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建商业映射批量插入失败", e);
            }
        }
        if (CollectionUtil.isNotEmpty(empBussinessToInsert)) {
            // 使用 Set 去重，基于 msfxCustomerName 和 msfxCreditCode 组合
            Map<String, HbsBusinessMapping> uniqueMappings = new HashMap<>();
            for (HbsBusinessMapping mapping : empBussinessToInsert) {
                String key = mapping.getMsfxCustomerName() + "_" + mapping.getMsfxCreditCode();
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsBusinessMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsBusinessMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsBusinessMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsBusinessMapping::getMsfxCustomerName, record.getMsfxCustomerName())
                            .eq(HbsBusinessMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCustomerName()), HbsBusinessMapping::getHbsCustomerName, record.getHbsCustomerName())
                            .eq(StringUtils.isNotEmpty(record.getHbsTaxNumber()), HbsBusinessMapping::getHbsTaxNumber, record.getHbsTaxNumber())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsBusinessMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsBusinessMappingService.count(checkQuery) > 0) {
                        log.info("已存在商业相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsBusinessMappingService.save(record);
                }
            } catch (Exception e) {
                log.error("新建商业映射批量插入失败", e);
            }
        }
        //终端映射
        if (CollectionUtil.isNotEmpty(mappingsTerminalToInsert)) {
            // 使用 Set 去重，基于 hbsItemId 字段
            Map<String, HbsTerminalMapping> uniqueMappings = new HashMap<>();
            for (HbsTerminalMapping mapping : mappingsTerminalToInsert) {
                String key = generateTerminalMappingKey(mapping.getHbsItemId(), mapping.getMsfxEnterpriseName(), mapping.getMsfxCreditCode(), mapping.getHbsTerminalName(), mapping.getHbsCreditCode());
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsTerminalMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsTerminalMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsTerminalMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsTerminalMapping::getMsfxEnterpriseName, record.getMsfxEnterpriseName())
                            .eq(HbsTerminalMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCreditCode()), HbsTerminalMapping::getHbsCreditCode, record.getHbsCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsTerminalName()), HbsTerminalMapping::getHbsTerminalName, record.getHbsTerminalName())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsTerminalMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsTerminalMappingService.count(checkQuery) > 0) {
                        log.info("已存在终端相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsTerminalMappingService.save(record);
                }

            } catch (Exception e) {
                log.error("新建终端映射批量插入失败", e);
            }
        }
        if (CollectionUtil.isNotEmpty(empTerminalToInsert)) {
            // 使用 Set 去重，基于 msfxCustomerName 和 msfxCreditCode 组合
            Map<String, HbsTerminalMapping> uniqueMappings = new HashMap<>();
            for (HbsTerminalMapping mapping : empTerminalToInsert) {
                String key = mapping.getMsfxEnterpriseName() + "_" + mapping.getMsfxCreditCode();
                uniqueMappings.put(key, mapping);
            }
            // 将去重后的映射列表转换为新的列表
            List<HbsTerminalMapping> uniqueList = new ArrayList<>(uniqueMappings.values());
            // 执行批量插入
            try {
                for (HbsTerminalMapping record : uniqueList) {
                    // 检查是否已存在相同的映射
                    LambdaQueryWrapper<HbsTerminalMapping> checkQuery = new LambdaQueryWrapper<>();
                    checkQuery.eq(HbsTerminalMapping::getMsfxEnterpriseName, record.getMsfxEnterpriseName())
                            .eq(HbsTerminalMapping::getMsfxCreditCode, record.getMsfxCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsCreditCode()), HbsTerminalMapping::getHbsCreditCode, record.getHbsCreditCode())
                            .eq(StringUtils.isNotEmpty(record.getHbsTerminalName()), HbsTerminalMapping::getHbsTerminalName, record.getHbsTerminalName())
                            .eq(StringUtils.isNotEmpty(record.getHbsItemId()), HbsTerminalMapping::getHbsItemId, record.getHbsItemId());
                    if (hbsTerminalMappingService.count(checkQuery) > 0) {
                        log.info("已存在终端相同的映射，跳过插入：{}", record);
                        continue;
                    }
                    hbsTerminalMappingService.save(record);
                }

                log.info("成功插入 {} 条新建终端映射记录", uniqueList.size());
            } catch (Exception e) {
                log.error("新建终端映射批量插入失败", e);
            }
        }

    }

    /**
     * 产品匹配
     */
    @Override
    public boolean matchAndSaveProduct(List<HbsProductMapping> mappingsProductToInsert,
                                       List<HbsProductMapping> empProductToInsert, SingleSalesInventoryRecords record) {
        Boolean isMatched = false;
        // 检查必要字段是否为空
        if (StringUtils.isEmpty(record.getDrugGenericName())) {
            return false;
        }
        // 提取 Excel 规格中的括号内容
        String excelSpec = extractSpecificationFromExcel(record.getSpecification());

        // 查询hbs产品档案中的匹配项
        LambdaQueryWrapper<ProductArchive> productQuery = new LambdaQueryWrapper<>();
        productQuery.eq(ProductArchive::getProductName, record.getDrugGenericName());
        productQuery.eq(ProductArchive::getSpecification, excelSpec); // 使用提取后的规格
        productQuery.eq(ProductArchive::getSalesStatus, "在售");
        List<ProductArchive> existingProducts = productArchiveService.list(productQuery);
        if (CollectionUtil.isNotEmpty(existingProducts)) {
            for (ProductArchive existingProduct : existingProducts) {
                // 创建映射关系
                HbsProductMapping mapping = HbsProductMapping.builder()
                        .msfxGenericName(record.getDrugGenericName())
                        .msfxSpecification(record.getSpecification())
                        .msfxPackageSpec(record.getPackageSpec())
                        .build();

                // 根据单位类型调整字段
                if ("1".equals(record.getSmallestUnitCode())) {
                    // 盒：使用包装规格
                    Integer calculatedPiecesPerBox = calculatePiecesPerBox(existingProduct.getPackagingSpec());
                    if (calculatedPiecesPerBox != null) {
                        Integer excelPiecesPerBox = parsePiecePerBox(record.getPackageSpec());
                        if (excelPiecesPerBox != null && excelPiecesPerBox.equals(calculatedPiecesPerBox)) {
                            mapping.setHbsItemId(existingProduct.getId());
                            mapping.setHbsProductName(existingProduct.getProductName());
                            mapping.setHbsSpecification(existingProduct.getSpecification());
                            mapping.setHbsPackageSpec(existingProduct.getPackagingSpec());
                        }
                    }
                } else if ("2".equals(record.getSmallestUnitCode())) {
                    // 瓺：使用最小制剂数量
                    Integer piecesPerBottle = parsePiecePerBottle(existingProduct.getPackagingSpec());
                    if (piecesPerBottle != null) {
                        Integer excelPiecePerBottle = parsePiecePerBottle(record.getPackageSpec());
                        if (excelPiecePerBottle != null && excelPiecePerBottle.equals(piecesPerBottle)) {
                            mapping.setHbsItemId(existingProduct.getId());
                            mapping.setHbsProductName(existingProduct.getProductName());
                            mapping.setHbsSpecification(existingProduct.getSpecification());
                            mapping.setHbsPackageSpec(existingProduct.getPackagingSpec());
                        }
                    }
                }
                mappingsProductToInsert.add(mapping);
            }
            isMatched = true;
        } else {
            HbsProductMapping newProduct = HbsProductMapping.builder()
                    .msfxGenericName(record.getDrugGenericName())
                    .msfxSpecification(record.getSpecification())
                    .msfxPackageSpec(record.getPackageSpec())
                    .build();
            empProductToInsert.add(newProduct);
            isMatched = true;

        }
        return isMatched;
    }

    /**
     * 从Excel中的规格字段中提取规格信息
     */
    private String extractSpecificationFromExcel(String specification) {
        if (StringUtils.isEmpty(specification)) {
            return null;
        }
        // 使用正则表达式提取括号内的内容
        Pattern pattern = Pattern.compile("\\(([^)]+)\\)");
        Matcher matcher = pattern.matcher(specification);
        if (matcher.find()) {
            return matcher.group(1).trim();
        }
        return specification.trim();
    }

    /**
     * 解析Excel规格中的"片/盒"或"片/瓶"数量
     *
     * @param spec 如 "21片/盒"
     * @return 数值
     */
    private Integer parsePiecePerBox(String spec) {
        if (StringUtils.isEmpty(spec)) {
            return null;
        }
        Pattern pattern = Pattern.compile("(\\d+)片/盒");
        Matcher matcher = pattern.matcher(spec);
        if (matcher.find()) {
            return Integer.parseInt(matcher.group(1));
        }
        return null;
    }

    /**
     * 计算HBS包装规格中每盒含有的总片数或支数（适用于"盒"的情况）
     *
     * @param packagingSpec 如 "铝塑/10片/2板/300盒" 或 "10ml:0.25g/安瓿/6支/80盒"
     * @return 总片数或支数
     */
    private Integer calculatePiecesPerBox(String packagingSpec) {
        if (StringUtils.isEmpty(packagingSpec)) {
            return null;
        }

        // 情况1: 处理包含"片"和"板"的规格，如"铝塑/10片/2板/300盒"
        Pattern piecePattern = Pattern.compile("(\\d+)片");
        Pattern boardPattern = Pattern.compile("(\\d+)板");
        Matcher pieceMatcher = piecePattern.matcher(packagingSpec);
        Matcher boardMatcher = boardPattern.matcher(packagingSpec);

        if (pieceMatcher.find() && boardMatcher.find()) {
            int pieces = Integer.parseInt(pieceMatcher.group(1));
            int boards = Integer.parseInt(boardMatcher.group(1));
            return pieces * boards;
        }

        // 情况2: 处理包含"支"的规格，如"10ml:0.25g/安瓿/6支/80盒"
        Pattern unitPattern = Pattern.compile("(\\d+)支");
        Matcher unitMatcher = unitPattern.matcher(packagingSpec);
        if (unitMatcher.find()) {
            return Integer.parseInt(unitMatcher.group(1));
        }

        return null;
    }


    /**
     * 解析包装规格中的"片/瓶"数量（适用于"瓶"的情况）
     *
     * @param packagingSpec 如 "25mg/片100片/瓶300瓶/箱"
     * @return 数值
     */
    private Integer parsePiecePerBottle(String packagingSpec) {
        if (StringUtils.isEmpty(packagingSpec)) {
            return null;
        }
        Pattern pattern = Pattern.compile("(\\d+)片/瓶");
        Matcher matcher = pattern.matcher(packagingSpec);
        if (matcher.find()) {
            return Integer.parseInt(matcher.group(1));
        }
        return null;
    }


    // 生成映射键的方法
    private String generateMappingKey(String itemId, String msfxGenericName, String msfxSpecification, String mafxPackageSpec,
                                      String hbsProductName, String hbsSpecification, String hbsPackageSpec) {
        return itemId + msfxGenericName + "_" + msfxSpecification + "_" + mafxPackageSpec + "_" +
                hbsProductName + "_" + hbsSpecification + "_" + hbsPackageSpec;
    }


    /**
     * 商业匹配
     */
    @Override
    public boolean matchAndSaveBusiness(List<HbsBusinessMapping> mappingsBussinessToInsert,
                                        List<HbsBusinessMapping> empBussinessToInsert, SingleSalesInventoryRecords record) {
        Boolean isMatched = false;
        // 1.发货单位类型“批发企业” 或 “物流企业”
        if ("2".equals(record.getSenderUnitType()) || "5".equals(record.getSenderUnitType())) {
            // 查询商业档案
            List<HbsBusinessArchive> existingHbsBusinessArchives = findBusinessArchives(
                    record.getSenderCreditCode(),
                    record.getSenderUnit()
            );
            if (CollectionUtil.isNotEmpty(existingHbsBusinessArchives)) {
                // 收集需要插入的商业映射
                for (HbsBusinessArchive existingBusiness : existingHbsBusinessArchives) {
                    HbsBusinessMapping newHbsBusinessMapping = HbsBusinessMapping.builder()
                            .hbsItemId(existingBusiness.getId())
                            .msfxCustomerName(record.getSenderUnit())
                            .msfxCreditCode(record.getSenderCreditCode())
                            .hbsCustomerName(existingBusiness.getCustomerName())
                            .hbsTaxNumber(existingBusiness.getTaxId())
                            .build();
                    mappingsBussinessToInsert.add(newHbsBusinessMapping);
                }
            }
        } else {
            HbsBusinessMapping newHbsBusinessMapping = HbsBusinessMapping.builder()
                    .msfxCustomerName(record.getSenderUnit())
                    .msfxCreditCode(record.getSenderCreditCode())
                    .build();
            empBussinessToInsert.add(newHbsBusinessMapping);
            isMatched = true;
        }
        // 收货单位类型“批发企业” 或 “物流企业”
        if ("2".equals(record.getReceiverUnitType()) || "5".
                equals(record.getReceiverUnitType())) {
            // 查询商业档案
            List<HbsBusinessArchive> existingHbsBusinessArchives = findBusinessArchives(
                    record.getReceiverCreditCode(),
                    record.getReceiverUnit()
            );

            if (CollectionUtil.isNotEmpty(existingHbsBusinessArchives)) {
                for (HbsBusinessArchive existingBusiness : existingHbsBusinessArchives) {
                    HbsBusinessMapping newHbsBusinessMapping = HbsBusinessMapping.builder()
                            .hbsItemId(existingBusiness.getId())
                            .msfxCustomerName(record.getReceiverUnit())
                            .msfxCreditCode(record.getReceiverCreditCode())
                            .hbsCustomerName(existingBusiness.getCustomerName())
                            .hbsTaxNumber(existingBusiness.getTaxId())
                            .build();
                    mappingsBussinessToInsert.add(newHbsBusinessMapping);
                }
            }
            isMatched = true;
        } else {
            HbsBusinessMapping newHbsBusinessMapping = HbsBusinessMapping.builder()
                    .msfxCustomerName(record.getReceiverUnit())
                    .msfxCreditCode(record.getReceiverCreditCode())
                    .build();
            empBussinessToInsert.add(newHbsBusinessMapping);
            isMatched = true;
        }
        return isMatched;
    }

    // 生成商业映射键的方法
    private String generateBusinessMappingKey(String itemId, String msfxCustomerName, String msfxCreditCode,
                                              String hbsCustomerName, String hbsTaxNumber) {
        return itemId + msfxCustomerName + "_" + msfxCreditCode + "_" + hbsCustomerName + "_" + hbsTaxNumber;
    }

    /**
     * 使用批量查询减少数据库交互次数
     */
    private List<HbsBusinessArchive> findBusinessArchives(String creditCode, String customerName) {
        List<HbsBusinessArchive> result = new ArrayList<>();
        // 优先按客户名称查询
        if (StringUtils.isNotEmpty(customerName)) {
            LambdaQueryWrapper<HbsBusinessArchive> nameQuery = new LambdaQueryWrapper<>();
            nameQuery.eq(HbsBusinessArchive::getCustomerName, customerName);
            List<HbsBusinessArchive> byName = hbsBusinessArchiveService.list(nameQuery);

            if (CollectionUtil.isNotEmpty(byName)) {
                return byName;
            }
        }

        // 如果客户名称查询不到且有税号，则按税号查询
        if (StringUtils.isNotEmpty(creditCode)) {
            LambdaQueryWrapper<HbsBusinessArchive> taxIdQuery = new LambdaQueryWrapper<>();
            taxIdQuery.eq(HbsBusinessArchive::getTaxId, creditCode);
            return hbsBusinessArchiveService.list(taxIdQuery);
        }

        return result;
    }

    /**
     * 去除重复的商业映射和终端映射
     */
    @Transactional(rollbackFor = Exception.class)
    public void deleRepeat() {
        // 先执行更新操作
        hbsTerminalMappingMapper.updateDuplicateRecords();
        hbsBusinessMappingMapper.updateDuplicateRecords();
        // 再执行删除操作
        hbsTerminalMappingMapper.deleteDuplicateRecordsByName();
        hbsBusinessMappingMapper.deleteDuplicateRecordsByName();

    }

    /**
     * 匹配并保存终端
     *
     * @param record
     * @return
     */
    @Override
    public boolean matchAndSaveTerminal(List<HbsTerminalMapping> mappingsTerminalToInsert, List<HbsTerminalMapping> empTerminalToInser, SingleSalesInventoryRecords record) {
        Boolean isMatched = false;
        // 发货单位类型“零售” 或 “医疗单位”
        if ("1".equals(record.getSenderUnitType()) || "6".equals(record.getSenderUnitType())) {
            // 查询终端档案
            List<HbsTerminalArchive> existingHbsTerminalArchives = findTerminalArchives(
                    record.getSenderCreditCode(),
                    record.getSenderUnit()
            );
            if (CollectionUtil.isNotEmpty(existingHbsTerminalArchives)) {
                for (HbsTerminalArchive existingTerminal : existingHbsTerminalArchives) {
                    HbsTerminalMapping newHbsTerminalMapping = HbsTerminalMapping.builder()
                            .hbsItemId(existingTerminal.getId())
                            .msfxEnterpriseName(record.getSenderUnit())
                            .msfxCreditCode(record.getSenderCreditCode())
                            .hbsTerminalName(existingTerminal.getTerminalName())
                            .hbsCreditCode(existingTerminal.getSocialCreditCode())
                            .build();
                    mappingsTerminalToInsert.add(newHbsTerminalMapping);
                }
                isMatched = true;
            } else {
                HbsTerminalMapping newHbsTerminalMapping = HbsTerminalMapping.builder()
                        .msfxEnterpriseName(record.getSenderUnit())
                        .msfxCreditCode(record.getSenderCreditCode())
                        .build();
                empTerminalToInser.add(newHbsTerminalMapping);
            }
            isMatched = true;
        }

        // 收货单位类型“零售” 或 “医疗单位”
        if ("1".equals(record.getReceiverUnitType()) || "6".equals(record.getReceiverUnitType())) {
            // 查询终端档案
            List<HbsTerminalArchive> existingHbsTerminalArchives = findTerminalArchives(
                    record.getReceiverCreditCode(),
                    record.getReceiverUnit()
            );

            if (CollectionUtil.isNotEmpty(existingHbsTerminalArchives)) {
                // 收集需要插入的终端映射
                for (HbsTerminalArchive existingTerminal : existingHbsTerminalArchives) {
                    HbsTerminalMapping newHbsTerminalMapping = HbsTerminalMapping.builder()
                            .hbsItemId(existingTerminal.getId())
                            .msfxEnterpriseName(record.getReceiverUnit())
                            .msfxCreditCode(record.getReceiverCreditCode())
                            .hbsTerminalName(existingTerminal.getTerminalName())
                            .hbsCreditCode(existingTerminal.getSocialCreditCode())
                            .build();
                    mappingsTerminalToInsert.add(newHbsTerminalMapping);
                }
                isMatched = true;
            } else {
                HbsTerminalMapping newHbsTerminalMapping = HbsTerminalMapping.builder()
                        .msfxEnterpriseName(record.getReceiverUnit())
                        .msfxCreditCode(record.getReceiverCreditCode())
                        .build();
                empTerminalToInser.add(newHbsTerminalMapping);
                isMatched = true;
            }
        }
        return isMatched;
    }

    // 生成终端映射键的方法
    private String generateTerminalMappingKey(String itemId, String msfxEnterpriseName, String msfxCreditCode,
                                              String hbsTerminalName, String hbsCreditCode) {
        return itemId + msfxEnterpriseName + "_" + msfxCreditCode + "_" + hbsTerminalName + "_" + hbsCreditCode;
    }


    // 使用批量查询减少数据库交互次数
    private List<HbsTerminalArchive> findTerminalArchives(String creditCode, String enterpriseName) {
        List<HbsTerminalArchive> result = new ArrayList<>();

        // 如果信用代码查询不到且有企业名称，则按企业名称查询
        if (StringUtils.isNotEmpty(enterpriseName)) {
            LambdaQueryWrapper<HbsTerminalArchive> nameQuery = new LambdaQueryWrapper<>();
            nameQuery.eq(HbsTerminalArchive::getTerminalName, enterpriseName);
            List<HbsTerminalArchive> enterpriseNamelist = hbsTerminalArchiveService.list(nameQuery);

            if (CollectionUtil.isNotEmpty(enterpriseNamelist)) {
                return enterpriseNamelist;
            }
        }
        // 优先按信用代码查询
        if (StringUtils.isNotEmpty(creditCode)) {
            LambdaQueryWrapper<HbsTerminalArchive> creditCodeQuery = new LambdaQueryWrapper<>();
            creditCodeQuery.eq(HbsTerminalArchive::getSocialCreditCode, creditCode);
            return hbsTerminalArchiveService.list(creditCodeQuery);
        }

        // 如果信用代码查询不到且有企业名称，则按企业名称查询
        if (StringUtils.isNotEmpty(enterpriseName)) {
            LambdaQueryWrapper<HbsTerminalArchive> nameQuery = new LambdaQueryWrapper<>();
            nameQuery.eq(HbsTerminalArchive::getTerminalName, enterpriseName);
            return hbsTerminalArchiveService.list(nameQuery);
        }

        return result;
    }


}
